
import pandas as pd
import plotly
plotly.offline.init_notebook_mode()


t0 = pd.read_csv("negocios/todos.csv")

t1 = pd.read_csv("negocios/negocios_criados.csv")

t2 = pd.read_csv("negocios/agendamentos.csv")

t3 = pd.read_csv("negocios/agentamentos_aceitos.csv")

t4 = pd.read_csv("negocios/diagnostico.csv")

t5 = pd.read_csv("negocios/proposta.csv")

t6 = pd.read_csv("negocios/cliente.csv")

# t7 = pd.read_excel("data/06 cliente.xlsx", sheet_name='Contagem de Negócios   Cliente ')


t0_t1 = t0.merge(t1, on='Record ID', how='left')


t0_t1 = t0_t1.rename(columns={
    'Nome do negócio_x': 'Nome do negócio',
    'Etapa do negócio_x': 'Etapa do negócio',
    'Valor na moeda da empresa_x': 'Valor na moeda da empresa',
    'Nome do negócio_y':'flag_corresponde_1',
    'Data de fechamento_x': 'Data de fechamento',
    'Pipeline_x': 'Pipeline',
}).assign(flag_corresponde_1 = lambda df_: df_['flag_corresponde_1'].notnull().map({True: "Sim", False: "Nao"}))\
    .drop(columns=['Valor na moeda da empresa_y', 'Etapa do negócio_y', 'Data de fechamento_y'])


t0_t1_t2 = t0_t1.merge(t2, on='Record ID', how='left')

t0_t1_t2 = t0_t1_t2.rename(columns={
    'Nome do negócio_x': 'Nome do negócio',
    'Etapa do negócio_x': 'Etapa do negócio',
    'Valor na moeda da empresa_x': 'Valor na moeda da empresa',
    'Nome do negócio_y':'flag_corresponde_2',
    'Data de fechamento_x': 'Data de fechamento',
    'Pipeline_x': 'Pipeline',
}).assign(flag_corresponde_2 = lambda df_: df_['flag_corresponde_2'].notnull().map({True: "Sim", False: "Nao"}))\
    .drop(columns=['Valor na moeda da empresa_y', 'Etapa do negócio_y', 'Data de fechamento_y', 'Pipeline_y'])


t0_t1_t2_t3 = t0_t1_t2.merge(t3, on='Record ID', how='left')\
    .rename(columns={
        'Nome do negócio_x': 'Nome do negócio',
        'Valor na moeda da empresa_x': 'Valor na moeda da empresa',
        'Etapa do negócio_x': 'Etapa do negócio',
        'Data de fechamento_x': 'Data de fechamento',
        'Nome do negócio_y': 'flag_corresponde_3',
        'Pipeline_x': 'Pipeline',
    })\
        .assign(flag_corresponde_3 = lambda df_: df_['flag_corresponde_3'].notnull().map({True: "Sim", False: "Nao"}))\
            .drop(columns=['Valor na moeda da empresa_y', 'Etapa do negócio_y', 'Data de fechamento_y', 'Pipeline_y'])


t0_t1_t2_t3_t4 = t0_t1_t2_t3.merge(t4, on='Record ID', how='left')\
    .rename(columns={
        'Nome do negócio_x': 'Nome do negócio',
        'Valor na moeda da empresa_x': 'Valor na moeda da empresa',
        'Etapa do negócio_x': 'Etapa do negócio',
        'Data de fechamento_x': 'Data de fechamento',
        'Nome do negócio_y': 'flag_corresponde_4',
        'Pipeline_x': 'Pipeline',
    }).assign(flag_corresponde_4 = lambda df_: df_['flag_corresponde_4'].notnull().map({True: "Sim", False: "Nao"}))\
    .drop(columns=['Valor na moeda da empresa_y', 'Etapa do negócio_y', 'Data de fechamento_y', 'Pipeline_y'])


t0_t1_t2_t3_t4_t5 = t0_t1_t2_t3_t4.merge(t5, on='Record ID', how='left')\
    .rename(columns={
        'Nome do negócio_x': 'Nome do negócio',
        'Valor na moeda da empresa_x': 'Valor na moeda da empresa',
        'Etapa do negócio_x': 'Etapa do negócio',
        'Data de fechamento_x': 'Data de fechamento',
        'Nome do negócio_y': 'flag_corresponde_5',
        'Pipeline_x': 'Pipeline',
    }).assign(flag_corresponde_5 = lambda df_: df_['flag_corresponde_5'].notnull().map({True: "Sim", False: "Nao"}))\
    .drop(columns=['Valor na moeda da empresa_y', 'Etapa do negócio_y', 'Data de fechamento_y', 'Pipeline_y'])


t0_t1_t2_t3_t4_t5_t6 = t0_t1_t2_t3_t4_t5.merge(t6, on='Record ID', how='left')\
    .rename(columns={
        'Nome do negócio_x': 'Nome do negócio',
        'Valor na moeda da empresa_x': 'Valor na moeda da empresa',
        'Etapa do negócio_x': 'Etapa do negócio',
        'Data de fechamento_x': 'Data de fechamento',
        'Nome do negócio_y': 'flag_corresponde_6',
        'Pipeline_x': 'Pipeline',
    }).assign(flag_corresponde_6 = lambda df_: df_['flag_corresponde_6'].notnull().map({True: "Sim", False: "Nao"}))\
    .drop(columns=['Valor na moeda da empresa_y', 'Etapa do negócio_y', 'Data de fechamento_y', 'Pipeline_y'])


t0_t1_t2_t3_t4_t5_t6 = t0_t1_t2_t3_t4_t5_t6\
    .assign(**{"Nome do negócio": lambda df_: df_['Nome do negócio'].str.strip()})\
    .rename(
    columns={
        'flag_corresponde_1': 'Negocios_Criados',
        'flag_corresponde_2': 'Agendamentos',
        'flag_corresponde_3': 'Agendamento_Aceito',
        'flag_corresponde_4': 'Diagnostico',
        'flag_corresponde_5': 'Proposta',
        'flag_corresponde_6': 'Cliente'
    }
)

#  'flag_corresponde_1': 'Agendamentos',
#         'flag_corresponde_2': 'Agendamento_Aceito',
#         'flag_corresponde_3': 'Diagnostico',
#         'flag_corresponde_4': 'Proposta',
#         'flag_corresponde_5': 'Cliente'


empresas = pd.read_csv("negocios/empresas.csv")

negocios = pd.read_csv("negocios/todos.csv")

negocios = negocios.loc[:, ['Record ID', 'Nome do negócio', 'Valor', 'Valor do contrato anual', 'Motivo de fechamento perdido']]\
.assign(**{'Nome do negócio': lambda df: df['Nome do negócio'].str.upper()})

empresas = empresas.dropna(subset=['Nome da empresa'])
empresas = empresas.loc[:, ['Record ID', 'Nome da empresa', 'Setor']].drop("Record ID", axis=1)

empresas = empresas.assign(**{
    'Nome da empresa': lambda df_: df_['Nome da empresa'].str.strip()
})
empresas['Nome da empresa'] = empresas['Nome da empresa'].str.upper()


negocios_record_nome = negocios.loc[:, ['Record ID', 'Nome do negócio']].assign(
    **{'Nome do negócio': lambda df_: df_['Nome do negócio'].str.strip().str.split(' - ').str[0].str.replace("NEGÓCIO",'')}
)


import fuzzy_pandas as fpd


# remove -
t0_t1_t2_t3_t4_t5_t6 = t0_t1_t2_t3_t4_t5_t6.assign(
    **{"Nome do negócio": lambda df: df['Nome do negócio'].str.replace('-', ' ')}
)


t0_t1_t2_t3_t4_t5_t6 = t0_t1_t2_t3_t4_t5_t6.assign(
    **{"Nome do negócio": lambda df: df['Nome do negócio'].apply(lambda s: ' '.join(s.split(' ')[:2]))}
)
empresas = empresas.assign(
    **{'Nome da empresa': lambda df: df['Nome da empresa'].apply(lambda s: ' '.join(s.split(' ')[:2]))}
)


funil_negocios = t0_t1_t2_t3_t4_t5_t6.merge(negocios, on='Record ID', how='left')\
    .rename(columns={
        'Nome do negócio_x': 'Nome do negócio',
        })\
    .drop(columns=['Nome do negócio_y'])\
    .assign(**{'Nome do negócio': lambda df: df['Nome do negócio'].str.upper()})


# fuzzy matching

results = fpd.fuzzy_merge(funil_negocios, empresas, left_on='Nome do negócio', right_on='Nome da empresa',
                          join='left-outer',
                          method = 'levenshtein', threshold = 0.8)
    # .merge(empresas.assign(**{'Nome da empresa': lambda df: df['Nome da empresa'].str.upper()}), left_on='Nome do negócio', right_on='Nome da empresa', how='left')\
    # .rename(columns={
    #     'Record ID_x': "Record ID"
    # })\
    # .loc[:, ['Record ID', 'Nome do negócio', 'Data de criação', 'Data de fechamento', 'ultima_etapa', 'Valor', 'Valor do contrato anual', 'Motivo de fechamento perdido', 'Setor']]


print("Found", results.shape)
results.sample(15)

Found (492, 26)


merged = results.merge(negocios_record_nome, on='Record ID', how='left')


merged.loc[merged['Setor'].isnull()].loc[:, ['Record ID', 'Nome do negócio_y']].to_csv("negocios_sem_setor_completo.csv", index=False)


negocios_setor = pd.read_csv("negocios_sem_setor_completo.csv")


results = results.merge(negocios_setor, on='Record ID', how='left').assign(
    Setor = lambda df: df['Setor_x'].combine_first(df['Setor_y'])
).drop(columns=['Setor_x', 'Setor_y'])


results.to_csv("negocios_completo.csv", index=False)


results.columns

Index(['Record ID', 'Nome do negócio', 'Data de criação', 'Data de fechamento',
       'Etapa do negócio', 'Valor', 'Valor do contrato anual',
       'Receita anual recorrente', 'Valor total do contrato', 'Valor previsto',
       'Valor na moeda da empresa',
       'Valor do negócio projetado na moeda local',
       'Valor do negócio projetado', 'Motivo de fechamento perdido',
       'Nome da empresa', 'Setor', 'ultima_etapa', 'Cliente',
       'Nome do negócio completo'],
      dtype='object')


results['ultima_etapa'] = results[['Negocios_Criados','Agendamentos', 'Agendamento_Aceito', 'Diagnostico', 'Proposta', 'Cliente']].apply(
    lambda row: next((coluna for coluna in ['Cliente', 'Proposta', 'Diagnostico', 'Agendamento_Aceito', 'Agendamentos', 'Negocios_Criados'] if row[coluna] == 'Sim'), 'Contato'), axis=1)


results = results.drop(columns=['Valor_y', 'Valor do contrato anual_y','Pipeline', 'Motivo de fechamento perdido_y',
                      'Negocios_Criados','Agendamentos', 'Agendamento_Aceito', 'Diagnostico', 'Proposta', 'Cliente']).rename(columns={
    'Valor_x': 'Valor',
    'Valor do contrato anual_x': 'Valor do contrato anual',
    'Nome do negócio_y': 'Nome do negócio completo',
    'Motivo de fechamento perdido_x': 'Motivo de fechamento perdido',
    })#.to_csv("negocios/silver/negocios_ultima_etapa.csv", index=False)


import pandas as pd


results = pd.read_csv("negocios/silver/negocios_ultima_etapa.csv")


results = results.drop(columns='Nome do negócio completo').merge(t0.loc[:, ['Record ID', 'Nome do negócio']], on='Record ID', how='left').rename(columns={
    'Nome do negócio_x': 'Nome do negócio',
    'Nome do negócio_y': 'Nome do negócio completo'
})


serie_dividida = results['Motivo de fechamento perdido'].fillna('Desconhecido').str.split(';')

# Crie uma nova série com cada motivo em uma linha separada
nova_serie = pd.Series([motivo.strip() for motivos in serie_dividida for motivo in motivos])

nova_serie.value_counts().sort_values().plot(kind='barh', figsize=(10, 5))

<Axes: >


# results['Nome do negócio completo'] = results['Nome do negócio completo'].combine_first(results['Nome do negócio'])


import pandas as pd
import plotly.express as px


# Converter a coluna 'Data' para o tipo datetime
results['Data de criação'] = pd.to_datetime(results['Data de criação'])
results['Setor'] = results['Setor'].str.strip()
results['Cliente'] = (results['ultima_etapa'] == 'Cliente').map({True: 'Sim', False: 'Nao'})


fig = px.scatter(results, x='Data de criação', y='Setor', color='Cliente', symbol='Cliente',
                 color_discrete_map={'Sim': 'green', 'Nao': 'red'},
                 title='Gráfico de Setores por Data',
                 labels={'Data': 'Data', 'Setor': 'Setor', 'Cliente': 'Cliente'},
                 category_orders={'Setor': ['A', 'B', 'C'], 'Cliente': ['Sim', 'Não']},
                 hover_data=['Nome do negócio completo'],
                 width=1000, height=1100)

# Configuração de formatação do eixo X
fig.update_xaxes(showgrid=True, tickangle=45)

# Exibir o gráfico interativo
fig.show()


import chart_studio.plotly as py
import chart_studio

chart_studio.tools.set_credentials_file(username = 'levyvix',   api_key = 'UqhjaEHoOL14JH5joQAT')


# py.plot(fig, filename = 'grafico_setores_por_data', auto_open=True)

'https://plotly.com/~levyvix/1/'


# taxa de conversao por segmento (setor)
# quantos entraram e quantos virarm clientes
import plotly.graph_objects as go

taxa_conversao_setor = (results.groupby("Setor")['Cliente'].value_counts().unstack().fillna(0).assign(
    **{'Taxa de conversao': lambda df: df['Sim'] / (df['Sim'] + df['Nao'])}
)
.sort_values(by='Taxa de conversao', ascending=False)
)

fig = go.Figure()

# Adicionar as barras para cada setor
fig.add_trace(go.Bar(x=taxa_conversao_setor.index, y=taxa_conversao_setor['Taxa de conversao'],
                     marker_color='blue', text=taxa_conversao_setor['Taxa de conversao'].round(2), textposition='auto'))

# Configurações do layout do gráfico
fig.update_layout(title='Taxa de Conversão por Setor',
                  xaxis_title='Setor',
                  yaxis_title='Taxa de Conversão',
                  xaxis=dict(tickangle=45))

# Exibir o gráfico interativo
fig.show()


# py.plot(fig, filename = 'taxa_de_conversao_por_segmento', auto_open=True)

'https://plotly.com/~levyvix/3/'


import pandas as pd
import plotly.express as px

for segment, group in results.groupby('Setor'):

  fig = px.scatter(group, x='Data de criação', y='Nome do negócio completo',
                   color='Cliente', symbol='Cliente', hover_data=['Data de criação'])

  fig.add_trace(px.scatter(group, x='Data de fechamento', y='Nome do negócio completo',
                hover_data=['Data de fechamento']).data[0])
               
  for name in group['Nome do negócio completo'].unique():
    df_name = group[group['Nome do negócio completo'] == name]
    
    creation_date = df_name['Data de criação'].iat[0]
    closing_date = df_name['Data de fechamento'].iat[0]

    fig.add_trace(px.line(x=[creation_date, closing_date], y=[name, name]).data[0])
    
    fig.update_traces(line=dict(color='rgb(100,100,100)', width=1), selector=dict(type='line'))

  fig.update_layout(title=f'Start and End Dates for {segment}')

  fig.show()


# py.plot(fig, filename = 'criacao_fechamento_segmento', auto_open=True)

'https://plotly.com/~levyvix/6/'

	Record ID	Nome do negócio	Data de criação	Data de fechamento	Etapa do negócio	Valor_x	Valor do contrato anual_x	Receita anual recorrente	Valor total do contrato	Valor previsto	...	Agendamentos	Agendamento_Aceito	Diagnostico	Proposta	Cliente	Valor_y	Valor do contrato anual_y	Motivo de fechamento perdido_y	Nome da empresa	Setor
255	7059246430	BANCO NEXT	2021-12-01 17:43	2021-12-31 17:43	Negócio perdido	808140.00	NaN	NaN	NaN	808140.00	...	Nao	Nao	Nao	Nao	Nao	808140.00	NaN	Timing; Desinteresse/Declinou	BANCO NEXT	NaN
376	4668934210	EUROFARMA	2021-03-15 17:45	2021-07-15 12:10	Negócio perdido	276000.00	NaN	NaN	NaN	276000.00	...	Sim	Sim	Sim	Sim	Nao	276000.00	NaN	Nós Declinamos	EUROFARMA	Farmacêutica
25	13912182170	DENTSPLY SIRONA	2023-06-29 14:54	NaN	Negócio perdido	NaN	NaN	NaN	NaN	NaN	...	Sim	Sim	Nao	Nao	Nao	NaN	NaN	Financeiro; Timing; Não é ICP	DENTSPLY SIRONA	NaN
353	5239041433	SYNGENTA	2021-05-12 11:02	2021-06-21 14:25	Cliente	187500.00	NaN	NaN	NaN	187500.00	...	Sim	Sim	Sim	Sim	Sim	187500.00	NaN	NaN	SYNGENTA	NaN
28	13898389069	VITTIA	2023-06-28 09:40	NaN	Proposta Enviada (SQL)	186000.00	NaN	NaN	NaN	186000.00	...	Sim	Sim	Sim	Sim	Nao	186000.00	NaN	NaN	VITTA	NaN
472	5691587426	NIDERA	2021-07-15 10:49	2022-02-15 15:10	Negócio perdido	NaN	NaN	NaN	NaN	NaN	...	Sim	Sim	Sim	Nao	Nao	NaN	NaN	Sumiu
66	12688511828	VERDE AGRITECH	2023-03-23 12:58	NaN	Negócio perdido	NaN	NaN	NaN	NaN	NaN	...	Sim	Sim	Nao	Nao	Nao	NaN	NaN	Maturação; Timing	VERDE AGRITECH	NaN
338	5381991819	OTTOBOCK	2021-06-01 10:43	2021-09-22 14:02	Negócio perdido	251055.60	NaN	NaN	NaN	251055.60	...	Sim	Sim	Sim	Sim	Sim	251055.60	NaN	Financeiro; Desinteresse/Declinou	OTTOBOCK	Farmacêutica
260	6939016495	ORAL SIN	2021-11-16 17:20	2022-05-17 16:25	Negócio perdido	458150.16	NaN	NaN	NaN	458150.16	...	Sim	Sim	Sim	Sim	Nao	458150.16	NaN	Fechou com Concorrente; Financeiro	ORAL SIN	NaN
367	4985313037	SANTA HELENA	2021-04-12 14:41	2021-05-31 12:47	Negócio perdido	227936.16	NaN	NaN	NaN	227936.16	...	Sim	Sim	Sim	Sim	Nao	227936.16	NaN	Fechou com Concorrente; Financeiro	SANTA HELENA	Produção de alimentos
116	10358562966	NEW HOLLAND	2022-09-29 15:53	NaN	Negócio perdido	NaN	NaN	NaN	NaN	NaN	...	Sim	Sim	Sim	Nao	Nao	NaN	NaN	Timing; Maturação	NEW HOLLAND	Maquinário
7	14109118720	HUDL	2023-07-13 11:46	NaN	Agendamentos (MQL)	NaN	NaN	NaN	NaN	NaN	...	Sim	Nao	Nao	Nao	Nao	NaN	NaN	NaN	HUDL	Software de computador
411	3086522280	EUROFARMA	2020-10-08 14:56	2021-01-12 14:34	Negócio perdido	20964.00	NaN	NaN	NaN	20964.00	...	Sim	Sim	Sim	Sim	Sim	20964.00	NaN	Não era decisor	EUROFARMA	Farmacêutica
185	8437713017	EUCATUR	2022-04-01 15:20	2022-04-06 11:49	Negócio perdido	NaN	NaN	NaN	NaN	NaN	...	Sim	Sim	Nao	Nao	Nao	NaN	NaN	Financeiro; Não é ICP	EUCATUR	Transporte/caminhões/ferrovias
223	7992382761	DONIZETE DISTRIBUIDORA	2022-02-22 11:31	2022-04-28 14:26	Negócio perdido	NaN	NaN	NaN	NaN	NaN	...	Sim	Sim	Nao	Nao	Nao	NaN	NaN	Não é ICP; Sumiu; Nós Declinamos	ONIZ DISTRIBUIDORA	Atacado

Graficos¶